import pandas as pd,numpy as np,datetime,re

# df['是否优秀']=np.where((df.设备id==33722),'优','~')
writer = pd.ExcelWriter('1170.xlsx')

def format_col_width(ws):
    ws.column_dimensions['A'].width = 22.5
    ws.column_dimensions['B'].width = 37.5
    ws.column_dimensions['D'].width = 22.5

def offline1170(excel_name,proj_name):
    df = pd.read_excel('C:/Users/0717/Downloads/%s.xlsx' % excel_name).drop(['设备id','描述'], axis=1)
    # df = df[~ df['最后上线时间'].str.contains('0001')]
    # df['最后上线时间'] = pd.to_datetime(df['最后上线时间'], format='%Y-%m-%d')
    # s_date = datetime.datetime.strptime('2021-%s 00:00:00' % day, '%Y-%m-%d %H:%M:%S').date()  # 起始日期
    # df = df[df['最后上线时间'].dt.date >= s_date]
    # for item in (df.设备编号).str[0:12]:
    #     if(item in x):
    #         print('item',df.index,item)
    #         df = df[~ df['最后上线时间'].str.contains(item)]
    #     else:
    #         x.append(item)
    # df.drop(df.index[0:4], inplace=True)
    # print(df)
    # print(df.index[3])
    df = df[~ df['连接状态'].str.contains('在线')]
    df.reset_index(drop=True,inplace=True)
    x = []
    index_list = []
    for t, s in df.iterrows():
        temp_str = s[0][0:12]
        if(temp_str in x):
            # print(t,temp_str)
            index_list.append(t)
            # df.drop(df.index[t],inplace=True)
        else:
            x.append(temp_str)
    df.drop(df.index[index_list], inplace=True)
    df.sort_values(by='最后上线时间', ascending=False,inplace=True)
    df.to_excel(writer, sheet_name=proj_name, index=False)
    worksheet = writer.sheets[proj_name]
    format_col_width(worksheet)
    writer.save()

county_dict = {'设备表':'益阳+10','设备表 (1)':'湘西+20','设备表 (2)':'长沙+20','设备表 (3)':'邵阳',
               '设备表 (4)':'张家界+第一批',
               '设备表 (5)':'株洲，3+10','设备表 (6)':'常德+省厅','设备表 (7)':'娄底'}
# county_dict = {'设备表':'益阳','设备表 (1)':'湘西','设备表 (2)':'长沙','设备表 (3)':'邵阳'}
for k,v in county_dict.items():
    # offline1170(k,v,'08-13')
    offline1170(k, v)
# offline1170('设备表 (3)','长沙')